package cn.sugarscat.db;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class DB {
    private final Connection connection;
    private final Statement statement;
    private PreparedStatement pstmt = null;

    public DB() {
        // 读取 application.properties 文件，获取数据库连接设置
        // datasource.driver-class-name
        // datasource.url
        // datasource.username
        // datasource.password
        Properties properties = new Properties();
        try {
            FileInputStream in = new FileInputStream("mysql.properties");
            properties.load(in);
            in.close();
        } catch (IOException e) {
            System.out.println("Failed to load mysql.properties:");
            throw new RuntimeException(e);
        }
        String className = properties.getProperty("mysql.driver-class-name");
        String url = properties.getProperty("mysql.url");
        String username = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");
        try{
            Class.forName(className);
        }catch(ClassNotFoundException e){
            System.out.println("Failed to load driver class:" + e.getMessage());
        }try{
            this.connection = DriverManager.getConnection(url, username, password);
            this.statement = this.connection.createStatement();
        }catch(SQLException e) {
            System.out.println("Failed to connect DB:");
            throw new RuntimeException(e);
        }
    }

    // 执行插入语句
    public int executeInsert(String sql){
        int num = 0;
        try{
            num = this.statement.executeUpdate(sql);
        }
        catch(SQLException e){
            System.out.println("Failed to insert:" + e.getMessage());
        }
        return num;
    }

    // 执行无条件查询语句
    public ResultSet executeQuery(String sql){
        ResultSet res = null;
        try{
            res = this.statement.executeQuery(sql);
        }
        catch(SQLException e){
            System.out.println("Failed to query:" + e.getMessage());
        }
        return res;
    }

    // 执行有1个条件查询语句
    public ResultSet executeQuery(String sql, String condition){
        ResultSet res = null;
        try{
            this.pstmt = this.connection.prepareStatement(sql);
            this.pstmt.setString(1, condition);
            res = this.pstmt.executeQuery();
        }
        catch(SQLException e){
            System.out.println("Failed to query:" + e.getMessage());
        }
        return res;
    }

    // 执行有2个条件查询语句
    public ResultSet executeQuery(String sql, String condition1, String condition2){
        ResultSet res = null;
        try{
            this.pstmt = this.connection.prepareStatement(sql);
            this.pstmt.setString(1, condition1);
            this.pstmt.setString(2, condition2);
            res = this.pstmt.executeQuery();
        }
        catch(SQLException e){
            System.out.println("Failed to query:" + e.getMessage());
        }
        return res;
    }

    // 执行有3个条件查询语句
    public ResultSet executeQuery(String sql, String condition1, String condition2, String condition3){
        ResultSet res = null;
        try{
            this.pstmt = this.connection.prepareStatement(sql);
            this.pstmt.setString(1, condition1);
            this.pstmt.setString(2, condition2);
            this.pstmt.setString(3, condition3);
            res = this.pstmt.executeQuery();
        }
        catch(SQLException e){
            System.out.println("Failed to query:" + e.getMessage());
        }
        return res;
    }

    // 执行无条件更新语句
    public int executeUpdate(String sql){
        int num = 0;
        try{
            num = this.statement.executeUpdate(sql);
        }catch(SQLException e){
            System.out.println("Failed to update:" + e.getMessage());
        }
        return num;
    }

    // 执行有1个条件更新语句
    public int executeUpdate(String sql, String condition){
        int num = 0;
        try{
            this.pstmt = this.connection.prepareStatement(sql);
            this.pstmt.setString(1, condition);
            num = this.pstmt.executeUpdate();
        }catch(SQLException e){
            System.out.println("Failed to update:" + e.getMessage());
        }
        return num;
    }

    // 执行有2个条件更新语句
    public int executeUpdate(String sql, String condition1, String condition2){
        int num = 0;
        try{
            this.pstmt = this.connection.prepareStatement(sql);
            this.pstmt.setString(1, condition1);
            this.pstmt.setString(2, condition2);
            num = this.pstmt.executeUpdate();
        }catch(SQLException e){
            System.out.println("Failed to update:" + e.getMessage());
        }
        return num;
    }

    // 执行无条件删除语句
    public int executeDelete(String sql){
        int num = 0;
        try{
            num = this.statement.executeUpdate(sql);
        }
        catch(SQLException e){
            System.out.println("Failed to delete:" + e.getMessage());
        }
        return num;
    }

    // 执行有条件删除语句
    public int executeDelete(String sql, String condition){
        int num = 0;
        try{
            this.pstmt = this.connection.prepareStatement(sql);
            this.pstmt.setString(1, condition);
            num = this.pstmt.executeUpdate();
        }
        catch(SQLException e){
            System.out.println("Failed to delete:" + e.getMessage());
        }
        return num;
    }

    // 关闭数据库的连接
    public void closeDB(){
        try{
            this.statement.close();
            this.connection.close();
            if (this.pstmt != null)
                this.pstmt.close();
        }
        catch(Exception e){
            System.out.println("Fail to close DB:" + e.getMessage());
        }
    }
}
